using AdminSG3L.Applicaticn.Dtos;
using AdminSG3L.Applicaticn.Commands;
using AdminSG3L.Applicaticn.ServicesInterface;
using Microsoft.Extensions.Configuration;
using Serilog;
using Npgsql;

namespace AdminSG3L.Applicaticn.Services;

/// <summary>
/// 日志服务实现
/// </summary>
public class LogService : ILogService
{
    private readonly string _connectionString;

    public LogService(IConfiguration configuration)
    {
        _connectionString = configuration.GetConnectionString("Postgres") 
            ?? throw new InvalidOperationException("PostgreSQL连接字符串未配置");
    }

    public async Task<ApiResponse<(IEnumerable<LogDto> Logs, int TotalCount)>> GetPagedLogsAsync(
        int pageIndex, int pageSize, LogSearchDto? search = null)
    {
        try
        {
            using var connection = new NpgsqlConnection(_connectionString);
            await connection.OpenAsync();

            // 构建WHERE条件
            var whereConditions = new List<string>();
            var parameters = new List<NpgsqlParameter>();

            if (!string.IsNullOrEmpty(search?.Level))
            {
                whereConditions.Add("\"level\" = @Level");
                parameters.Add(new NpgsqlParameter("@Level", ConvertLevelToInt(search.Level)));
            }

            if (!string.IsNullOrEmpty(search?.Message))
            {
                whereConditions.Add("\"message\" ILIKE @Message");
                parameters.Add(new NpgsqlParameter("@Message", $"%{search.Message}%"));
            }

            if (search?.StartDate.HasValue == true)
            {
                whereConditions.Add("\"timestamp\" >= @StartDate");
                parameters.Add(new NpgsqlParameter("@StartDate", search.StartDate.Value));
            }

            if (search?.EndDate.HasValue == true)
            {
                whereConditions.Add("\"timestamp\" <= @EndDate");
                parameters.Add(new NpgsqlParameter("@EndDate", search.EndDate.Value));
            }

            var whereClause = whereConditions.Count > 0 ? $"WHERE {string.Join(" AND ", whereConditions)}" : "";

            // 获取总数
            var countSql = $"SELECT COUNT(*) FROM \"applogs\" {whereClause}";
            using var countCommand = new NpgsqlCommand(countSql, connection);
            foreach (var param in parameters)
            {
                countCommand.Parameters.Add(param);
            }
            var totalCountResult = await countCommand.ExecuteScalarAsync();
            var totalCount = totalCountResult != null ? (int)(long)totalCountResult : 0;

            // 获取分页数据
            var dataSql = $@"
                SELECT ""timestamp"", ""level"", ""message"", ""exception"", ""message_template"", ""log_event""
                FROM ""applogs"" 
                {whereClause}
                ORDER BY ""timestamp"" DESC 
                LIMIT @PageSize OFFSET @Offset";

            using var dataCommand = new NpgsqlCommand(dataSql, connection);
            foreach (var param in parameters)
            {
                dataCommand.Parameters.Add(new NpgsqlParameter(param.ParameterName, param.Value));
            }
            dataCommand.Parameters.AddWithValue("@PageSize", pageSize);
            dataCommand.Parameters.AddWithValue("@Offset", pageIndex * pageSize);

            var logs = new List<LogDto>();
            using var reader = await dataCommand.ExecuteReaderAsync();
            while (await reader.ReadAsync())
            {
                var log = new LogDto(
                    Id: Guid.NewGuid(),
                    Timestamp: reader.GetDateTime(0),
                    Level: ConvertLevelFromInt(reader.GetInt32(1)),
                    Message: reader.GetString(2),
                    Exception: reader.IsDBNull(3) ? null : reader.GetString(3),
                    Properties: reader.IsDBNull(4) ? null : reader.GetString(4),
                    LogEvent: reader.IsDBNull(5) ? null : reader.GetString(5),
                    Source: "System",
                    User: "System"
                );
                logs.Add(log);
            }

            return ApiResponse<(IEnumerable<LogDto>, int)>.Success((logs, totalCount));
        }
        catch (Exception ex)
        {
            Log.Error(ex, "获取日志列表失败");
            return ApiResponse<(IEnumerable<LogDto>, int)>.Fail($"获取日志列表失败: {ex.Message}");
        }
    }

    public async Task<ApiResponse<LogStatsDto>> GetLogStatsAsync(LogSearchDto? search = null)
    {
        try
        {
            using var connection = new NpgsqlConnection(_connectionString);
            await connection.OpenAsync();

            var sql = @"
                SELECT ""level"", COUNT(*) as count
                FROM ""applogs"" 
                GROUP BY ""level""";

            using var command = new NpgsqlCommand(sql, connection);
            var stats = new Dictionary<string, int>();
            
            using var reader = await command.ExecuteReaderAsync();
            while (await reader.ReadAsync())
            {
                var levelInt = reader.GetInt32(0);
                var count = reader.GetInt64(1);
                var levelName = ConvertLevelFromInt(levelInt);
                stats[levelName] = (int)count;
            }

            var logStats = new LogStatsDto(
                ErrorCount: stats.GetValueOrDefault("Error", 0),
                WarningCount: stats.GetValueOrDefault("Warning", 0),
                InfoCount: stats.GetValueOrDefault("Information", 0),
                DebugCount: stats.GetValueOrDefault("Debug", 0),
                TotalCount: stats.Values.Sum()
            );

            return ApiResponse<LogStatsDto>.Success(logStats);
        }
        catch (Exception ex)
        {
            Log.Error(ex, "获取日志统计失败");
            return ApiResponse<LogStatsDto>.Fail($"获取日志统计失败: {ex.Message}");
        }
    }

    public async Task<ApiResponse<LogDto?>> GetLogByIdAsync(Guid id)
    {
        try
        {
            using var connection = new NpgsqlConnection(_connectionString);
            await connection.OpenAsync();

            var sql = @"
                SELECT ""timestamp"", ""level"", ""message"", ""exception"", ""message_template"", ""log_event""
                FROM ""applogs"" 
                ORDER BY ""timestamp"" DESC 
                LIMIT 1";

            using var command = new NpgsqlCommand(sql, connection);
            using var reader = await command.ExecuteReaderAsync();
            
            if (await reader.ReadAsync())
            {
                var log = new LogDto(
                    Id: id,
                    Timestamp: reader.GetDateTime(0),
                    Level: ConvertLevelFromInt(reader.GetInt32(1)),
                    Message: reader.GetString(2),
                    Exception: reader.IsDBNull(3) ? null : reader.GetString(3),
                    Properties: reader.IsDBNull(4) ? null : reader.GetString(4),
                    LogEvent: reader.IsDBNull(5) ? null : reader.GetString(5),
                    Source: "System",
                    User: "System"
                );

                return ApiResponse<LogDto?>.Success(log);
            }

            return ApiResponse<LogDto?>.Fail("日志不存在");
        }
        catch (Exception ex)
        {
            Log.Error(ex, "获取日志详情失败");
            return ApiResponse<LogDto?>.Fail($"获取日志详情失败: {ex.Message}");
        }
    }

    public async Task<ApiResponse<int>> ClearLogsAsync(ClearLogsDto clearLogsDto)
    {
        try
        {
            using var connection = new NpgsqlConnection(_connectionString);
            await connection.OpenAsync();

            string sql = clearLogsDto.ClearAll ? 
                "DELETE FROM \"applogs\"" : 
                "DELETE FROM \"applogs\" WHERE \"timestamp\" < @BeforeDate";

            using var command = new NpgsqlCommand(sql, connection);
            if (!clearLogsDto.ClearAll && clearLogsDto.BeforeDate.HasValue)
            {
                command.Parameters.AddWithValue("@BeforeDate", clearLogsDto.BeforeDate.Value);
            }

            var deletedCount = await command.ExecuteNonQueryAsync();
            return ApiResponse<int>.Success(deletedCount, $"成功清除 {deletedCount} 条日志");
        }
        catch (Exception ex)
        {
            Log.Error(ex, "清除日志失败");
            return ApiResponse<int>.Fail($"清除日志失败: {ex.Message}");
        }
    }

    public async Task<ApiResponse<IEnumerable<LogDto>>> ExportLogsAsync(LogSearchDto? search = null)
    {
        try
        {
            using var connection = new NpgsqlConnection(_connectionString);
            await connection.OpenAsync();

            var sql = @"
                SELECT ""timestamp"", ""level"", ""message"", ""exception"", ""message_template"", ""log_event""
                FROM ""applogs"" 
                ORDER BY ""timestamp"" DESC 
                LIMIT 1000";

            using var command = new NpgsqlCommand(sql, connection);
            var logs = new List<LogDto>();
            
            using var reader = await command.ExecuteReaderAsync();
            while (await reader.ReadAsync())
            {
                var log = new LogDto(
                    Id: Guid.NewGuid(),
                    Timestamp: reader.GetDateTime(0),
                    Level: ConvertLevelFromInt(reader.GetInt32(1)),
                    Message: reader.GetString(2),
                    Exception: reader.IsDBNull(3) ? null : reader.GetString(3),
                    Properties: reader.IsDBNull(4) ? null : reader.GetString(4),
                    LogEvent: reader.IsDBNull(5) ? null : reader.GetString(5),
                    Source: "System",
                    User: "System"
                );
                logs.Add(log);
            }

            return ApiResponse<IEnumerable<LogDto>>.Success(logs);
        }
        catch (Exception ex)
        {
            Log.Error(ex, "导出日志失败");
            return ApiResponse<IEnumerable<LogDto>>.Fail($"导出日志失败: {ex.Message}");
        }
    }

    private string ConvertLevelFromInt(int levelValue)
    {
        return levelValue switch
        {
            0 => "Verbose",
            1 => "Debug", 
            2 => "Information",
            3 => "Warning",
            4 => "Error",
            5 => "Fatal",
            _ => "Unknown"
        };
    }

    private int ConvertLevelToInt(string levelName)
    {
        return levelName switch
        {
            "Verbose" => 0,
            "Debug" => 1,
            "Information" => 2,
            "Warning" => 3,
            "Error" => 4,
            "Fatal" => 5,
            _ => 2 // 默认为Information
        };
    }
}
